package org.subatu.orm.util;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.subatu.orm.annotation.TableField;
import org.subatu.orm.annotation.TableId;
import org.subatu.orm.annotation.TableName;
import org.subatu.orm.entity.EntityBean;

import java.lang.reflect.Field;
import java.util.List;

/**
 * sql创建工具类
 */
public class SqlUtil {
    static Logger logger = LoggerFactory.getLogger(SqlUtil.class);

    private static final String insertSqlModel = "INSERT INTO @tableName (@columns) VALUES (@values) ";
    private static final String deleteSqlModel = "UPDATE @tableName set @logic WHERE @idsWhere ";
    private static final String deleteRealSqlModel = "DELETE FROM @tableName WHERE @idsWhere ";
    private static final String updateSqlModel = "UPDATE @tableName set @columnsSet WHERE @idsWhere @andLogic @andVersion";
    private static final String selectByIdSqlModel = "SELECT * FROM @tableName WHERE @idsWhere @andLogic ";
    private static final String totalCountSqlModel = "SELECT COUNT(*) cnt FROM ( @sql ) as count_table ";


    // 获取表名
    public static String getTableName(Class<?> clazz) {
        TableName tableNameAnnotation = clazz.getAnnotation(TableName.class);
        if (tableNameAnnotation != null) {
            return tableNameAnnotation.value();
        } else {
            // 类名 驼峰转下划线 表名
            return ConvertUtils.convertToSnakeCase(clazz.getSimpleName());
        }
    }

    // 获取属性的 表结构的 列名
    public static String getColumn(Field field) {
        String fieldName_ = ConvertUtils.convertToSnakeCase(field.getName()); // 字段转下划线

        if (field.isAnnotationPresent(TableField.class)) {
            TableField annotation = field.getAnnotation(TableField.class);
            return annotation.value() != null ? annotation.value() : fieldName_;
        }
        if (field.isAnnotationPresent(TableId.class)) {
            TableId annotation = field.getAnnotation(TableId.class);
            return annotation.value() != null ? annotation.value() : fieldName_;
        }

        return fieldName_;
    }

    // 获取ids的条件
    private static StringBuilder getIdsWhere(EntityBean entityBean) {
        List<Field> idFieldList = entityBean.getIdFieldList();
        StringBuilder idsWhere = new StringBuilder();
        for (Field idField : idFieldList) {
            idsWhere.append("and `").append(SqlUtil.getColumn(idField)).append("` = ? ");
        }
        idsWhere.delete(0, "and".length());
        return idsWhere;
    }

    // 获取 logic 匹配
    private static String getLogic(EntityBean entityBean, int value) {
        Field tableLogicField = entityBean.getTableLogicField();
        if (tableLogicField == null) {
            return "";
        }
        String logicSql = " `" + SqlUtil.getColumn(tableLogicField) + "`=" + value;
        return logicSql;
    }

    // 获取 version版本 匹配
    private static String getVersion(EntityBean entityBean) {
        Field versionField = entityBean.getVersionField();
        if (versionField == null) {
            return "";
        }
        String logicSql = " and `" + SqlUtil.getColumn(versionField) + "`= ? ";
        return logicSql;
    }

    /**
     * 生成 insert sql
     *
     * @param entityBean
     * @return
     */
    public static String makeInsertSql(EntityBean entityBean) {

        StringBuilder columnsSql = new StringBuilder("");
        StringBuilder valuesSql = new StringBuilder("");

        List<Field> allFieldList = entityBean.getAllFieldList();
        for (Field field : allFieldList) {
            String column = SqlUtil.getColumn(field);
            columnsSql.append('`').append(column).append("`,");
            valuesSql.append("?,");
        }

        columnsSql.deleteCharAt(columnsSql.length() - 1);
        valuesSql.deleteCharAt(valuesSql.length() - 1);

        String insertSql = insertSqlModel.replace("@tableName", entityBean.getTableName());
        insertSql = insertSql.replace("@columns", columnsSql);
        insertSql = insertSql.replace("@values", valuesSql);
        logger.debug("生成 insertSql:{}", insertSql);

        return insertSql;


    }


    // 生成真删除sql
    public static <T> String makeDeleteRealSql(EntityBean entityBean) {

        StringBuilder idsWhere = getIdsWhere(entityBean);

        String deleteRealSql = deleteRealSqlModel.replace("@tableName", entityBean.getTableName());
        deleteRealSql = deleteRealSql.replace("@idsWhere", idsWhere);
        logger.debug("生成 deleteRealSql:{}", deleteRealSql);
        return deleteRealSql;
    }


    public static <T> String makeSelectByIdSql(EntityBean entityBean) {
        StringBuilder idsWhere = getIdsWhere(entityBean);


        String selectByIdSql = selectByIdSqlModel.replace("@tableName", entityBean.getTableName());
        selectByIdSql = selectByIdSql.replace("@idsWhere", idsWhere);
        selectByIdSql = selectByIdSql.replace("@andLogic", " and " + SqlUtil.getLogic(entityBean, 0));
        logger.debug("生成 selectByIdSql :{}", selectByIdSql);

        return selectByIdSql;


    }

    public static <T> String makeDeleteSql(EntityBean entityBean) {
        StringBuilder idsWhere = getIdsWhere(entityBean);

        String deleteSql = deleteSqlModel.replace("@tableName", entityBean.getTableName());
        deleteSql = deleteSql.replace("@idsWhere", idsWhere);
        deleteSql = deleteSql.replace("@logic", SqlUtil.getLogic(entityBean, 1));
        logger.debug("生成 deleteSql:{}", deleteSql);
        return deleteSql;
    }

    public static <T> String makeUpdateSql(EntityBean entityBean) {
        StringBuilder idsWhere = getIdsWhere(entityBean);

        StringBuilder columnsSql = new StringBuilder("");

        List<Field> allFieldList = entityBean.getAllFieldList();
        for (Field field : allFieldList) {
            if (!entityBean.isGeneralColumn(field)) {
                // id 或者 logic
                continue;
            }
            String column = SqlUtil.getColumn(field);
            columnsSql.append('`').append(column).append("`= ? ").append(',');
        }
        columnsSql.deleteCharAt(columnsSql.length() - 1);

        String updateSql = updateSqlModel.replace("@tableName", entityBean.getTableName());
        updateSql = updateSql.replace("@columnsSet", columnsSql);
        updateSql = updateSql.replace("@idsWhere", idsWhere);
        updateSql = updateSql.replace("@andLogic", " and " + SqlUtil.getLogic(entityBean, 0));
        updateSql = updateSql.replace("@andVersion", SqlUtil.getVersion(entityBean));
        logger.debug("生成 updateSql:{}", updateSql);
        return updateSql;

    }

    // 普通sql 转成分页sql
    public static String makeSelectCountSql(String sql) {
        String totalCountSql = totalCountSqlModel.replace("@sql", sql);
        logger.debug("生成 totalCountSql:{}", totalCountSql);
        return totalCountSql;
    }


}
